<%-- 
    Document   : movieList
    Created on : May 1, 2013, 5:39:55 PM
    Author     : DevSteve
--%>

<%@page import="java.util.List"%>
<%@page import="java.io.FileReader"%>
<%@page import="java.io.IOException"%>
<%@page import="java.io.BufferedReader"%>
<%@page import="java.io.InputStreamReader"%>
<%@page import="java.io.FileNotFoundException"%>
<%@page import="java.io.FileInputStream"%>
<%@page import="java.io.File"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="db.DB"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
    <head>
        <title>Dataflix</title>
        <meta http-equiv="content-type" content="text/html; charset=UTF-8" />
        <meta http-equiv="content-language" content="en" />
        <meta name="description" content="" />
        <meta name="language" content="en" />
        <link type="text/css" rel="stylesheet" href="../css/style.css"/>
        <link type="text/css" rel="stylesheet" href="../css/style.css"/>
        <link type-="text/css" rel="stylesheet" href="../css/jquery-ui-1.9.2.custom.min.css"/>
        <link type-="text/css" rel="stylesheet" href="../css/timeline.css"/>
        <link type-="text/css" rel="stylesheet" href="../css/bootstrap.min.css"/>
        <style type="text/css">
            body {
                padding-bottom: 40px;
            }
            .sidebar-nav {
                padding: 9px 0;
            }
        </style>
        <script src="../js/jquery-1.9.1.min.js"></script>
        <script src="../js/jquery-ui-1.9.2.custom.min.js"></script>
        <script src="../js/bootstrap.min.js"></script>
        <script>

        </script>

    <head>
    <body>
        <%

            java.sql.Connection con;
            java.sql.Statement s;
            java.sql.Statement s2;
            java.sql.Statement s3;
            java.sql.Statement s4;
            java.sql.ResultSet rs;
            java.sql.ResultSet rs2;
            java.sql.ResultSet rs3;
            java.sql.ResultSet rs4;
            java.sql.PreparedStatement pst;

            con = null;
            s = null;
            pst = null;
            rs = null;

            DB data = new DB();

            String sql = "SELECT (SELECT COUNT(*) As levelZero FROM Users WHERE Users.Authority = 0 AND Users.Level =0), (SELECT COUNT(*) As levelZero FROM Users WHERE Users.Authority = 0 AND Users.Level =1),(SELECT COUNT(*) As levelZero FROM Users WHERE Users.Authority = 0 AND Users.Level =2),(SELECT COUNT(*) As levelZero FROM Users WHERE Users.Authority = 0 AND Users.Level =3),(SELECT (SELECT COUNT(*)*10 FROM Users WHERE Users.Authority = 0 AND Users.Level =0) + (SELECT COUNT(*)*15 FROM Users WHERE Users.Authority = 0 AND Users.Level =1) + (SELECT COUNT(*)*20 FROM Users WHERE Users.Authority = 0 AND Users.Level =2) + (SELECT COUNT(*)*25 FROM Users WHERE Users.Authority = 0 AND Users.Level =3))";
            try {
                con = data.getConnection();
                s = con.createStatement();
                rs = s.executeQuery(sql);

        %>
        <table id="head">
            <tr>
                <td>
                    <a href="../index.jsp"><img style="margin-left:10%;" src="../images/logoadmin.png"/></a>
                </td>

                <td>
                    <table id="loginbox">
                        <tr>
                            <td width="40%">
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
        </table>
        <div class="bottom"></div>
        <div class="container-fluid">
            <h3>
                Manager Page 
                <INPUT Type="BUTTON" class="btn" value="Movie list add/edit/delete" onclick="window.location.href = 'movieList.jsp';" />

                <INPUT Type="BUTTON"  class="btn" value="Employee list add/edit/delete" onclick="window.location.href = 'employeeList.jsp';" />

            </h3>

            <table class="table" >
                <tr>
                    <td>
                        <b>- Sales Report: </b>
                    </td>
                </tr>
                <tr>
                    <td>
                        <table class="table table-bordered" >
                            <thead>
                                <tr>
                                    <th width="100px;">Limited($10)</th>
                                    <th width="100px;">Unlimited-1($15)</th>
                                    <th width="100px;">Unlimited-2($20)</th>
                                    <th width="100px;">Unlimited-3($25)</th>
                                    <th width="100px;">Total</th>
                                </tr>
                            </thead>
                            <tr>
                                <%
                                    while (rs.next()) {
                                %>
                                <td>
                                    <%= rs.getString(1)%>    
                                </td>
                                <td>
                                    <%= rs.getString(2)%>  
                                </td>
                                <td>
                                    <%= rs.getString(3)%>  
                                </td>
                                <td>
                                    <%= rs.getString(4)%>  
                                </td>
                                <td>
                                    $ <%= rs.getString(5)%>
                                </td>
                                <% }%>
                            </tr>
                        </table>

                    </td>
                </tr>
                <tr>
                    <td>
                        <b>- Most Active Customers:</b>
                    </td>
                </tr>
                <tr>
                    <td>
                        <table class="table table-bordered" >
                            <thead>
                                <tr>
                                    <th width="20px;">Ranking</th>
                                    <th width="100px;">Customer Email Id</th>
                                    <th width="80px;">Points</th>
                                </tr>
                            </thead>
                            
                            <%
                                String sql2 ="SELECT Users.Email, COUNT(*)  FROM Rental INNER JOIN Users ON Users.Id = Rental.CustomerId  GROUP BY Rental.customerId order by COUNT(*) DESC LIMIT 3;";
                                s2 = con.createStatement();
                                rs2 = s2.executeQuery(sql2);
                                int i =1;
                                while (rs2.next()) {

                            %>
                                <tr>
                                    <td><%= i%></td>
                                    <td><%= rs2.getString(1)%></td>
                                    <td><%= rs2.getString(2)%></td>
                                </tr>
                           
                            <% i++;}%>
                        </table>
                    </td>
                </tr>
                <tr>
                    <td>
                        <b>- Most Active Movies:</b> 
                    </td>
                </tr>
                <tr>
                    <td>
                        <table class="table table-bordered" >
                            <thead>
                                <tr>
                                    <th width="20px;">Ranking</th>
                                    <th width="100px;">Movie Name</th>
                                    <th width="80px;">Points</th>
                                </tr>
                            </thead>
                             <%
                                String sql3 ="SELECT Movie.name, COUNT(*)  FROM Rental INNER JOIN Movie ON Movie.Id = Rental.MovieId  GROUP BY Rental.movieId order by COUNT(*) DESC LIMIT 3;";
                                s3 = con.createStatement();
                                rs3 = s3.executeQuery(sql3);
                                i =1;
                                while (rs3.next()) {

                            %>
                                <tr>
                                    <td><%= i%></td>
                                    <td><%= rs3.getString(1)%></td>
                                    <td><%= rs3.getString(2)%></td>
                                </tr>
                           
                            <% i++;}%>
                        </table>
                    </td>
                </tr>
                <tr>
                    <td>
                        <b>- Most Active Employees:</b> 
                    </td>
                </tr>
                <tr>
                    <td>
                        <table class="table table-bordered" >
                            <thead>
                                <tr>
                                    <th width="20px;">Ranking</th>
                                    <th width="100px;">Employee Name</th>
                                    <th width="80px;">Points</th>
                                </tr>
                            </thead>
                             <%
                                String sql4 ="SELECT Users.FirstName, COUNT(*)  FROM Rental INNER JOIN Users ON Users.Id = Rental.EmployeeId  GROUP BY Rental.EmployeeId order by COUNT(*) DESC LIMIT 3;";
                                s4 = con.createStatement();
                                rs4 = s4.executeQuery(sql4);
                                i =1;
                                while (rs4.next()) {

                            %>
                                <tr>
                                    <td><%= i%></td>
                                    <td><%= rs4.getString(1)%></td>
                                    <td><%= rs4.getString(2)%></td>
                                </tr>
                           
                            <% i++;}%>
                        </table>
                    </td>
                </tr>


                <%

                    } catch (Exception e) {
                        e.printStackTrace();
                    } finally {
                        if (rs != null) {
                            rs.close();
                        }
                        if (s != null) {
                            s.close();
                        }
                        if (con != null) {
                            con.close();
                        }
                    }

                %>
            </table>


        </div>
    <body>
</html>



